﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GFMS.MODEL;
using KPIS.GERP.GFMS.IDAL;

namespace KPIS.GERP.GFMS.DAL
{
    public sealed class D_MessageSend : IDAL_MessageSend
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();
        

        public D_MessageSend()
        {

        }

        public IList<M_MessageSend> GetFull(Nullable<int> to_emp_seq
            , Nullable<int> from_emp_seq, string message_status)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_MessageSend> infoList = new List<M_MessageSend>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@to_emp_seq", to_emp_seq);
                dbManager.AddParameters(2, "@from_emp_seq", from_emp_seq);
                dbManager.AddParameters(3, "@message_status", message_status);

                strSQL = "SELECT send.send_seq, send.send_date"
                    + ", send.ref_seq, send.ref_type, send.ref_no"
                    + ", send.send_text, send.from_emp_seq, send.to_emp_seq"
                    + ", send.from_send_seq, send.message_status"

                    + ", user1.user_name as from_user_name, user2.user_name as to_user_name"
                    + ", re.reply_seq, re.reply_type"
                    + ", CASE WHEN re.reply_type = '0' THEN 'ไม่เห็นชอบ'"
                        + " ELSE CASE WHEN re.reply_type = '1' THEN 'เห็นชอบ'"
                            + " ELSE CASE WHEN re.reply_type = '2' THEN 'ส่งต่อ'"
                                + " ELSE 'รอการพิจารณา'"
                                + " END"
                            + " END"
                        + " END AS reply_type_name"

                    + ", send.remarks, send.record_status"
                    + ", send.created_by, send.created_when, send.updated_by, send.updated_when"

                    + " FROM message_send send"
                    + " LEFT JOIN message_reply re ON send.send_seq = re.from_send_seq"
                    + " LEFT JOIN gerp_security.users user1 ON send.from_emp_seq = user1.user_seq"
                    + " LEFT JOIN gerp_security.users user2 ON send.to_emp_seq = user2.user_seq"
                    + " WHERE send.record_status not like @delete_status"
                    + (to_emp_seq != null ? " AND send.to_emp_seq = @to_emp_seq" : "")
                    + (from_emp_seq != null ? " AND send.from_emp_seq = @from_emp_seq" : "")
                    + (message_status != null ? " AND send.message_status = @message_status" : "")
                    + " ORDER BY send.send_date";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_MessageSend info = new M_MessageSend();
                        info.send_seq = Convert.ToInt32(drc[i]["send_seq"]);
                        info.send_date = cn.ConvertNullDateTime(drc[i]["send_date"]);
                        info.ref_seq = cn.ConvertNullInt(drc[i]["ref_seq"]);
                        info.ref_type = drc[i]["ref_type"].ToString();
                        info.ref_no = drc[i]["ref_no"].ToString();
                        info.send_text = drc[i]["send_text"].ToString();
                        info.from_emp_seq = cn.ConvertNullInt(drc[i]["from_emp_seq"]);
                        info.to_emp_seq = cn.ConvertNullInt(drc[i]["to_emp_seq"]);
                        info.from_send_seq = cn.ConvertNullInt(drc[i]["from_send_seq"]);
                        info.from_user_name = drc[i]["from_user_name"].ToString();
                        info.to_user_name = drc[i]["to_user_name"].ToString();
                        info.reply_seq = cn.ConvertNullInt(drc[i]["reply_seq"]);
                        info.reply_type = drc[i]["reply_type"].ToString();
                        info.reply_type_name = drc[i]["reply_type_name"].ToString();
                        info.remarks = drc[i]["remarks"].ToString();
                        info.message_status = drc[i]["message_status"].ToString();

                        info.record_status = drc[i]["record_status"].ToString();
                        info.created_by = cn.ConvertNullInt(drc[i]["created_by"]);
                        info.created_when = cn.ConvertNullDateTime(drc[i]["created_when"]);
                        info.updated_by = cn.ConvertNullInt(drc[i]["updated_by"]);
                        info.updated_when = cn.ConvertNullDateTime(drc[i]["updated_when"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_MessageSend> GetList(Nullable<int> to_emp_seq
            , Nullable<int> from_emp_seq, string message_status)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_MessageSend> infoList = new List<M_MessageSend>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@to_emp_seq", to_emp_seq);
                dbManager.AddParameters(2, "@from_emp_seq", from_emp_seq);
                dbManager.AddParameters(3, "@message_status", message_status);

                strSQL = "SELECT send.send_seq, send.send_text, send.send_date"
                    + " FROM message_send send"
                    + " WHERE send.record_status not like @delete_status"
                    + (to_emp_seq != null ? " AND send.to_emp_seq = @to_emp_seq" : "")
                    + (from_emp_seq != null ? " AND send.from_emp_seq = @from_emp_seq" : "")
                    + (message_status != null ? " AND send.message_status = @message_status" : "")
                    + " ORDER BY send.send_date";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_MessageSend info = new M_MessageSend();
                        info.send_seq = Convert.ToInt32(drc[i]["send_seq"]);
                        info.send_text = drc[i]["send_text"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public M_MessageSend GetInfo(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_MessageSend info = new M_MessageSend();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT send.send_seq, send.send_date"
                    + ", send.ref_seq, send.ref_type, send.ref_no"
                    + ", send.send_text, send.from_emp_seq, send.to_emp_seq"
                    + ", send.from_send_seq, send.message_status"

                    + ", user1.user_name as from_user_name, user2.user_name as to_user_name"
                    + ", re.reply_seq, re.reply_type"
                    + ", CASE WHEN re.reply_type = '0' THEN 'ไม่เห็นชอบ'"
                        + " ELSE CASE WHEN re.reply_type = '1' THEN 'เห็นชอบ'"
                            + " ELSE CASE WHEN re.reply_type = '2' THEN 'ส่งต่อ'"
                                + " ELSE 'รอการพิจารณา'"
                                + " END"
                            + " END"
                        + " END AS reply_type_name"

                    + ", send.remarks, send.record_status"
                    + ", send.created_by, send.created_when, send.updated_by, send.updated_when"

                    + " FROM message_send send"
                    + " LEFT JOIN message_reply re ON send.send_seq = re.from_send_seq"
                    + " LEFT JOIN gerp_security.users user1 ON send.from_emp_seq = user1.user_seq"
                    + " LEFT JOIN gerp_security.users user2 ON send.to_emp_seq = user2.user_seq"
                    + " WHERE send.send_seq = @seq";
                

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.send_seq = Convert.ToInt32(drc[0]["send_seq"]);
                    info.send_date = cn.ConvertNullDateTime(drc[0]["send_date"]);
                    info.ref_seq = cn.ConvertNullInt(drc[0]["ref_seq"]);
                    info.ref_type = drc[0]["ref_type"].ToString();
                    info.ref_no = drc[0]["ref_no"].ToString();
                    info.send_text = drc[0]["send_text"].ToString();
                    info.from_emp_seq = cn.ConvertNullInt(drc[0]["from_emp_seq"]);
                    info.to_emp_seq = cn.ConvertNullInt(drc[0]["to_emp_seq"]);
                    info.from_send_seq = cn.ConvertNullInt(drc[0]["from_send_seq"]);
                    info.from_user_name = drc[0]["from_user_name"].ToString();
                    info.to_user_name = drc[0]["to_user_name"].ToString();
                    info.reply_seq = cn.ConvertNullInt(drc[0]["reply_seq"]);
                    info.reply_type = drc[0]["reply_type"].ToString();
                    info.reply_type_name = drc[0]["reply_type_name"].ToString();
                    info.remarks = drc[0]["remarks"].ToString();
                    info.message_status = drc[0]["message_status"].ToString();

                    info.record_status = drc[0]["record_status"].ToString();
                    info.created_by = cn.ConvertNullInt(drc[0]["created_by"]);
                    info.created_when = cn.ConvertNullDateTime(drc[0]["created_when"]);
                    info.updated_by = cn.ConvertNullInt(drc[0]["updated_by"]);
                    info.updated_when = cn.ConvertNullDateTime(drc[0]["updated_when"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public M_MessageSend GetInfoFromRefSeq(int ref_seq, string ref_type)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_MessageSend info = new M_MessageSend();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@ref_seq", ref_seq);
                dbManager.AddParameters(1, "@ref_type", ref_type);
                dbManager.AddParameters(2, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT send.send_seq, send.send_date"
                    + ", send.ref_seq, send.ref_type, send.ref_no"
                    + ", send.send_text, send.from_emp_seq, send.to_emp_seq"
                    + ", send.from_send_seq, send.message_status"

                    + ", user1.user_name as from_user_name, user2.user_name as to_user_name"
                    + ", re.reply_seq, re.reply_type"
                    + ", CASE WHEN re.reply_type = '0' THEN 'ไม่เห็นชอบ'"
                        + " ELSE CASE WHEN re.reply_type = '1' THEN 'เห็นชอบ'"
                            + " ELSE CASE WHEN re.reply_type = '2' THEN 'ส่งต่อ'"
                                + " ELSE 'รอการพิจารณา'"
                                + " END"
                            + " END"
                        + " END AS reply_type_name"

                    + ", send.remarks, send.record_status"
                    + ", send.created_by, send.created_when, send.updated_by, send.updated_when"

                    + " FROM message_send send"
                    + " LEFT JOIN message_reply re ON send.send_seq = re.from_send_seq"
                    + " LEFT JOIN gerp_security.users user1 ON send.from_emp_seq = user1.user_seq"
                    + " LEFT JOIN gerp_security.users user2 ON send.to_emp_seq = user2.user_seq"
                    + " WHERE send.ref_seq = @ref_seq"
                    + " AND send.ref_type = @ref_type"
                    + " AND send.record_status not like @delete_status";
                

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.send_seq = Convert.ToInt32(drc[0]["send_seq"]);
                    info.send_date = cn.ConvertNullDateTime(drc[0]["send_date"]);
                    info.ref_seq = cn.ConvertNullInt(drc[0]["ref_seq"]);
                    info.ref_type = drc[0]["ref_type"].ToString();
                    info.ref_no = drc[0]["ref_no"].ToString();
                    info.send_text = drc[0]["send_text"].ToString();
                    info.from_emp_seq = cn.ConvertNullInt(drc[0]["from_emp_seq"]);
                    info.to_emp_seq = cn.ConvertNullInt(drc[0]["to_emp_seq"]);
                    info.from_send_seq = cn.ConvertNullInt(drc[0]["from_send_seq"]);
                    info.from_user_name = drc[0]["from_user_name"].ToString();
                    info.to_user_name = drc[0]["to_user_name"].ToString();
                    info.reply_seq = cn.ConvertNullInt(drc[0]["reply_seq"]);
                    info.reply_type = drc[0]["reply_type"].ToString();
                    info.reply_type_name = drc[0]["reply_type_name"].ToString();
                    info.remarks = drc[0]["remarks"].ToString();
                    info.message_status = drc[0]["message_status"].ToString();

                    info.record_status = drc[0]["record_status"].ToString();
                    info.created_by = cn.ConvertNullInt(drc[0]["created_by"]);
                    info.created_when = cn.ConvertNullDateTime(drc[0]["created_when"]);
                    info.updated_by = cn.ConvertNullInt(drc[0]["updated_by"]);
                    info.updated_when = cn.ConvertNullDateTime(drc[0]["updated_when"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public M_MessageSend GetInfoFromFromSendSeq(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_MessageSend info = new M_MessageSend();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT send.send_seq, send.send_date"
                    + ", send.ref_seq, send.ref_type, send.ref_no"
                    + ", send.send_text, send.from_emp_seq, send.to_emp_seq"
                    + ", send.from_send_seq, send.message_status"

                    + ", user1.user_name as from_user_name, user2.user_name as to_user_name"
                    + ", re.reply_seq, re.reply_type"
                    + ", CASE WHEN re.reply_type = '0' THEN 'ไม่เห็นชอบ'"
                        + " ELSE CASE WHEN re.reply_type = '1' THEN 'เห็นชอบ'"
                            + " ELSE CASE WHEN re.reply_type = '2' THEN 'ส่งต่อ'"
                                + " ELSE 'รอการพิจารณา'"
                                + " END"
                            + " END"
                        + " END AS reply_type_name"

                    + ", send.remarks, send.record_status"
                    + ", send.created_by, send.created_when, send.updated_by, send.updated_when"

                    + " FROM message_send send"
                    + " LEFT JOIN message_reply re ON send.send_seq = re.from_send_seq"
                    + " LEFT JOIN gerp_security.users user1 ON send.from_emp_seq = user1.user_seq"
                    + " LEFT JOIN gerp_security.users user2 ON send.to_emp_seq = user2.user_seq"
                    + " WHERE send.from_emp_seq = @seq"
                    + " AND send.record_status not like @delete_status";
                

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.send_seq = Convert.ToInt32(drc[0]["send_seq"]);
                    info.send_date = cn.ConvertNullDateTime(drc[0]["send_date"]);
                    info.ref_seq = cn.ConvertNullInt(drc[0]["ref_seq"]);
                    info.ref_type = drc[0]["ref_type"].ToString();
                    info.ref_no = drc[0]["ref_no"].ToString();
                    info.send_text = drc[0]["send_text"].ToString();
                    info.from_emp_seq = cn.ConvertNullInt(drc[0]["from_emp_seq"]);
                    info.to_emp_seq = cn.ConvertNullInt(drc[0]["to_emp_seq"]);
                    info.from_send_seq = cn.ConvertNullInt(drc[0]["from_send_seq"]);
                    info.from_user_name = drc[0]["from_user_name"].ToString();
                    info.to_user_name = drc[0]["to_user_name"].ToString();
                    info.reply_seq = cn.ConvertNullInt(drc[0]["reply_seq"]);
                    info.reply_type = drc[0]["reply_type"].ToString();
                    info.reply_type_name = drc[0]["reply_type_name"].ToString();
                    info.remarks = drc[0]["remarks"].ToString();
                    info.message_status = drc[0]["message_status"].ToString();

                    info.record_status = drc[0]["record_status"].ToString();
                    info.created_by = cn.ConvertNullInt(drc[0]["created_by"]);
                    info.created_when = cn.ConvertNullDateTime(drc[0]["created_when"]);
                    info.updated_by = cn.ConvertNullInt(drc[0]["updated_by"]);
                    info.updated_when = cn.ConvertNullDateTime(drc[0]["updated_when"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public DataTable GetCurrentApprover(Nullable<int> emp_seq, string process, int step_no)
        {
            try
            {
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@emp_seq", emp_seq);
                dbManager.AddParameters(1, "@process", process);
                dbManager.AddParameters(2, "@step_no", step_no);
                DataTable returnData = new DataTable();

                string sql = "select approve_path_seq, approver_type"
                    + ", exec_pos_group_seq, pos_seq, null as emp_seq"
                    + " from sys_approve_path path"
                    + " where process = @process"
                    + " and step_no = @step_no";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                bool bFound = false;

                if (dt != null && dt.Rows.Count > 0)
                {
                    DataTable dtb2 = new DataTable();

                    if (dt.Rows[0]["approver_type"].ToString() == "1")
                    {
                        int level = 1;

                        while (!bFound && level <= 5)
                        {
                            string strForm = "";

                            for (int i = 0; i < level; i++)
                            {
                                strForm = strForm + " left join hrm_mp_positions pos@1 on pos@2.working_upper_pos_seq = pos@1.pos_seq".Replace("@1", (i + 1).ToString()).Replace("@2", i.ToString());
                            }

                            strForm = strForm + " left join sys_exec_pos_group_dtls grp on pos@1.exec_pos_seq = grp.exec_pos_seq".Replace("@1", level.ToString());

                            sql = ("select emp2.emp_seq, pos@2.pos_seq, exef.exec_pos_grp_seq"
                                + (", " + dt.Rows[0]["approve_path_seq"].ToString() + " as approve_path_seq")

                                + " from hrm_ps_employees emp1"
                                + " inner join hrm_mp_positions pos0 on emp1.pos_seq = pos0.pos_seq"
                                + strForm
                                + " left join hrm_ps_employees emp2 on pos@2.pos_seq = emp2.pos_seq"
                                    + " and emp2.record_status <> 'D'"
                                + " inner join hrm_mp_exec_positions exef on pos@2.exec_pos_seq = exef.exec_pos_seq"
                                + " where emp1.emp_seq = @emp_seq").Replace("@2", level.ToString());
                            dtb2 = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];

                            if (dtb2.Rows.Count > 0)
                            {
                                bFound = dtb2.Rows[0]["exec_pos_grp_seq"].ToString() == dt.Rows[0]["exec_pos_group_seq"].ToString();
                                returnData = dtb2;
                            }
                            else
                            {
                                returnData = null;
                            }

                            level++;
                        }
                    }
                    else
                    {
                        sql = "select emp.emp_seq"
                            + ", " + dt.Rows[0]["approve_path_seq"].ToString() + " as approve_path_seq"
                            + " from hrm_ps_employees emp"
                            + " left join hrm_ps_employee_statuses es on emp.emp_status_seq = es.emp_status_seq"
                            + " where emp.pos_seq = " + dt.Rows[0]["pos_seq"].ToString()
                            + " and es.active = '1'";
                        dtb2 = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                        returnData = dtb2;
                        bFound = true;
                    }
                }
                else
                {
                    return null;
                }

                if (!bFound)
                {
                    step_no++;
                    returnData = GetCurrentApprover(emp_seq, process, step_no);
                }

                return returnData;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null)
                {
                    dbManager.Dispose();
                }
            }
        }

        public string GetNextStepNo(Nullable<int> approve_path_seq)
        {
            try
            {
                string strStepNo = "";
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@approve_path_seq", approve_path_seq);

                string sql = "select path2.step_no"
                    + " from sys_approve_path path"
                    + " inner join sys_approve_path path2 on path2.step_no = path.step_no + 1"
                        + " and path2.process = path.process"
                    + " where path.approve_path_seq = @approve_path_seq";
                dbManager.Open();
                DataTable dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];

                if (dt.Rows.Count > 0)
                {
                    strStepNo = dt.Rows[0]["step_no"].ToString();
                }
                else
                {
                    strStepNo = "0";
                }

                return strStepNo;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public int GetUnreadCount(int emp_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            int nCount = 0;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@emp_seq", emp_seq);
                dbManager.AddParameters(1, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT count(*) as unread_count"

                    + " FROM message_send send"
                    + " WHERE send.to_emp_seq = @emp_seq"
                    + " AND send.message_status = '0'"
                    + " AND send.record_status not like @delete_status";
                

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    nCount = Convert.ToInt32(drc[0]["unread_count"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return nCount;
        }

        public M_MessageSend GetLatestMessageStatus(Nullable<int> ref_seq, string ref_type)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_MessageSend info = new M_MessageSend();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@ref_seq", ref_seq);
                dbManager.AddParameters(2, "@ref_type", ref_type);

                strSQL = "SELECT send.send_seq, send.send_date, send2.send_seq"
                    + ", send.ref_seq, send.ref_type, send.ref_no, send.record_status"
                    + ", cast(concat(tonp.prefix_name, toemp.first_name, ' ', toemp.last_name) as char) as to_user_name"
                    + ", rep.reply_type, send.to_emp_seq, send.approve_path_seq"

                    + " FROM message_send send"
                    + " LEFT JOIN message_reply rep ON send.send_seq = rep.from_send_seq"
                        + " AND rep.record_status <> @delete_status"
                    + " LEFT JOIN message_send send2 ON send.send_seq = send2.from_send_seq"
                        + " AND send2.record_status <> @delete_status"
                    + " LEFT JOIN hrm_ps_employees toemp ON send.to_emp_seq = toemp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes tonp ON toemp.name_prefix_seq = tonp.name_prefix_seq"

                    + " WHERE send.record_status <> @delete_status"
                    + " and send2.send_seq is null"
                    + (ref_seq != null ? " AND send.ref_seq = @ref_seq" : "")
                    + (ref_type != null ? " AND send.ref_type = @ref_type" : "")
                    + " order by send.send_seq desc";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.send_seq = Convert.ToInt32(drc[0]["send_seq"]);
                    info.send_date = cn.ConvertNullDateTime(drc[0]["send_date"]);
                    info.ref_seq = cn.ConvertNullInt(drc[0]["ref_seq"]);
                    info.ref_type = drc[0]["ref_type"].ToString();
                    info.ref_no = drc[0]["ref_no"].ToString();
                    info.to_user_name = drc[0]["to_user_name"].ToString();
                    //info.send_text = drc[i]["send_text"].ToString();
                    //info.from_emp_seq = cn.ConvertNullInt(drc[i]["from_emp_seq"]);
                    info.to_emp_seq = cn.ConvertNullInt(drc[0]["to_emp_seq"]);
                    //info.from_send_seq = cn.ConvertNullInt(drc[i]["from_send_seq"]);
                    //info.from_user_name = drc[i]["from_user_name"].ToString();
                    //info.to_user_name = drc[i]["to_user_name"].ToString();
                    //info.reply_seq = cn.ConvertNullInt(drc[i]["reply_seq"]);
                    info.reply_type = drc[0]["reply_type"].ToString();
                    //info.reply_type_name = drc[i]["reply_type_name"].ToString();
                    //info.remarks = drc[i]["remarks"].ToString();
                    //info.message_status = drc[i]["message_status"].ToString();
                    info.approve_path_seq = cn.ConvertNullInt(drc[0]["approve_path_seq"]);

                    info.record_status = drc[0]["record_status"].ToString();
                    //info.created_by = cn.ConvertNullInt(drc[i]["created_by"]);
                    //info.created_when = cn.ConvertNullDateTime(drc[i]["created_when"]);
                    //info.updated_by = cn.ConvertNullInt(drc[i]["updated_by"]);
                    //info.updated_when = cn.ConvertNullDateTime(drc[i]["updated_when"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        //public string SelectRecentInsertedRoleSeq()
        //{
        //    string strSQL;
        //    string strResult;

        //    try
        //    {
        //        dbManager.Open();

        //        strSQL = "select scope_identity() as last_seq";

        //        strResult = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0]["last_seq"].ToString();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    return strResult;
        //}

        public string Insert(M_MessageSend info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(15);

                dbManager.AddParameters(0, "@send_seq", info.send_seq);
                dbManager.AddParameters(1, "@send_date", info.send_date);
                dbManager.AddParameters(2, "@ref_seq", info.ref_seq);
                dbManager.AddParameters(3, "@ref_type", info.ref_type);
                dbManager.AddParameters(4, "@ref_no", info.ref_no);
                dbManager.AddParameters(5, "@send_text", info.send_text);
                dbManager.AddParameters(6, "@from_emp_seq", info.from_emp_seq);
                dbManager.AddParameters(7, "@to_emp_seq", info.to_emp_seq);
                dbManager.AddParameters(8, "@from_send_seq", info.from_send_seq);
                dbManager.AddParameters(9, "@remarks", info.remarks);
                dbManager.AddParameters(10, "@message_status", info.message_status);

                dbManager.AddParameters(11, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(12, "@created_by", info.created_by);
                dbManager.AddParameters(13, "@created_when", info.created_when);
                dbManager.AddParameters(14, "@approve_path_seq", info.approve_path_seq);

                strSQL = "INSERT INTO message_send"
                    + "(send_date, ref_seq, ref_type, ref_no, send_text"
                    + ", from_emp_seq, to_emp_seq, from_send_seq, remarks, message_status, approve_path_seq"
                    + ", record_status, created_by, created_when)"
                    + " VALUES(@send_date"
                        + ", @ref_seq"
                        + ", @ref_type"
                        + ", @ref_no"
                        + ", @send_text"
                        + ", @from_emp_seq"
                        + ", @to_emp_seq"
                        + ", @from_send_seq"
                        + ", @remarks"
                        + ", @message_status"
                        + ", @approve_path_seq"

                        + ", @record_status"
                        + ", @created_by"
                        + ", @created_when);"
                    + " select scope_identity();";
                var strSeq = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0][0].ToString();
                return strSeq;
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(M_MessageSend info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(14);

                dbManager.AddParameters(0, "@send_seq", info.send_seq);
                dbManager.AddParameters(1, "@send_date", info.send_date);
                dbManager.AddParameters(2, "@ref_seq", info.ref_seq);
                dbManager.AddParameters(3, "@ref_type", info.ref_type);
                dbManager.AddParameters(4, "@ref_no", info.ref_no);
                dbManager.AddParameters(5, "@send_text", info.send_text);
                dbManager.AddParameters(6, "@from_emp_seq", info.from_emp_seq);
                dbManager.AddParameters(7, "@to_emp_seq", info.to_emp_seq);
                dbManager.AddParameters(8, "@from_send_seq", info.from_send_seq);
                dbManager.AddParameters(9, "@remarks", info.remarks);
                dbManager.AddParameters(10, "@message_status", info.message_status);

                dbManager.AddParameters(11, "@updated_by", info.updated_by);
                dbManager.AddParameters(12, "@updated_when", info.updated_when);
                dbManager.AddParameters(13, "@approve_path_seq", info.approve_path_seq);

                strSQL = "UPDATE message_send"
                    + " set send_date = @send_date"
                    + ", ref_seq = @ref_seq"
                    + ", ref_type = @ref_type"
                    + ", ref_no = @ref_no"
                    + ", send_text = @send_text"
                    + ", from_emp_seq = @from_emp_seq"
                    + ", to_emp_seq = @to_emp_seq"
                    + ", from_send_seq = @from_send_seq"
                    + ", remarks = @remarks"
                    + ", message_status = @message_status"
                    + ", approve_path_seq = @approve_path_seq"

                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE send_seq = @send_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(M_MessageSend info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@send_seq", info.send_seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE message_send"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE send_seq = @send_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateMessageStatus(M_MessageSend info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@send_seq", info.send_seq);
                dbManager.AddParameters(1, "@message_status", info.message_status);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE message_send"
                    + " SET message_status = @message_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE send_seq = @send_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
